package bluerain.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bluerain.model.MultiObject;

public class MultiObjectController {
	private static String DB_CLASS_NAME = "org.sqlite.JDBC";
	private static String CONNECTION_DB_NAME = "jdbc:sqlite:bluerain.db";
	private Connection conn;
	private Statement stat;
	
	/**
	 * This contructor initialize connections
	 * and statement, ready to make sql transactions
	 * and queries.
	 */
	public MultiObjectController() {
		try {
			Class.forName(DB_CLASS_NAME);
			String strConn = CONNECTION_DB_NAME;
			conn = DriverManager.getConnection(strConn); 
			stat = conn.createStatement(); 
		}
	    catch (Exception e) {
	    	e.printStackTrace();
		}
	}
	
	/**
	 * Create the multiObject table into SQLite
	 * database.
	 */
	public void create() {
		String strCreate = "";
		strCreate += "CREATE TABLE multiobject(";
		strCreate += "id, date, title, content, id_parent";
		strCreate += ");";
		
		try {	
			stat.executeUpdate(strCreate);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Get all MultiObject from database
	 * into a object list
	 * 
	 * @return List<MultiObject>
	 */
	public List<MultiObject> getAllMultiObjectList(){
		List<MultiObject> listMultiObjects = new ArrayList<MultiObject>();
		String strQuery = "";
		strQuery += "select * from multiobject;";
		
		try {
			ResultSet rs = stat.executeQuery(strQuery);
			while(rs.next()){
				MultiObject multiObject = new MultiObject();
				
				multiObject.setId(rs.getInt("id"));
				multiObject.setDate(rs.getString("date"));
				multiObject.setTitle(rs.getString("title"));
				multiObject.setContent(rs.getString("content"));
				multiObject.setIdParent(rs.getInt("id_parent"));
				
				listMultiObjects.add(multiObject);
			}
		} catch(Exception e){
			e.printStackTrace();
		}
		return listMultiObjects;
	}
	
	
	/**
	 * Save a MultiObject object getted
	 * by parameters.
	 * 
	 * @param MultiObjects
	 */
	public void save(MultiObject multiObject) {
		String strInsert = "";
		strInsert += "insert into multiobject values ";
		strInsert += "(?, ?, ?, ?, ?);";
				
		multiObject.setId(this.getAutoNumeric());
		try {
			PreparedStatement prep = conn.prepareStatement(strInsert);
	        prep.setInt(1, multiObject.getId()); 
	        prep.setString(2, multiObject.getDate());
	        prep.setString(3, multiObject.getTitle());
	        prep.setString(4, multiObject.getContent());
	        prep.setInt(5, multiObject.getIdParent());
	        
	        prep.addBatch(); 
	        prep.executeBatch(); 
	        conn.setAutoCommit(true);
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Delete a MultiObject object
	 * with it's id.
	 * 
	 * @param int
	 */
	public void delete(int intMultiObjectId){
		String strDeleteQuery = "";
		strDeleteQuery += "delete from multiobject where id = ";
		
		try {
			stat.executeUpdate(strDeleteQuery + intMultiObjectId);
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Get autonumeric id from MultiObjects.
	 * 
	 * @return int
	 */
	private int getAutoNumeric(){
		int intAutoNumeric = 1;
		String strQuery = "";
		strQuery += "select max(id)+1 from multiobject;";
		
		try {
			ResultSet rs = stat.executeQuery(strQuery);
			int intMaxId = rs.getInt(1);
			intAutoNumeric = intMaxId == 0 ? 1 : intMaxId;
		} catch(Exception e){
			e.printStackTrace();
		}
		
		return intAutoNumeric;
	}
}
